package grapid.database;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.TreeMap;

/**
 * 数据库操作类
 * 
 * @author Grapid
 * @version 2.0
 * 
 * @param <T>
 *            泛型类
 */
public abstract class GBaseDAO<T> {

	/**
	 * 取得数据库连接
	 * 
	 * @return 数据库连接
	 * @throws SQLException
	 */
	protected Connection getConnection() throws SQLException {
		return GConnectionManager.getConnection();
	}

	/**
	 * 执行一条sql语句
	 * 
	 * @param sql
	 *            sql语句
	 * @return 返回执行结果, true/false
	 */
	public boolean execute(String sql) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.execute(sql);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}
		return false;
	}

	/**
	 * 添加记录方法
	 * 
	 * @param sql
	 *            sql添加语句
	 * @return 返回成功数量
	 */
	public Integer create(String sql) {
		return create(sql, null);
	}

	/**
	 * 添加记录方法
	 * 
	 * @param sql
	 *            sql添加语句
	 * @param sqlInsertId
	 *            新插入id查询语句
	 * @return 返回成功数量 或 新插入ID
	 */
	public Integer create(String sql, String sqlInsertId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			int num = pstmt.executeUpdate();
			if (num > 0 && sqlInsertId != null) {
				closePreparedStatement(pstmt);
				pstmt = conn.prepareStatement(sqlInsertId);
				rs = pstmt.executeQuery();
				if (rs.next())
					return rs.getInt(1);
			}
			return num;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeResultSet(rs);
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}
		return 0;
	}

	/**
	 * 添加记录方法
	 * 
	 * @param query
	 *            CQuery类
	 * @return 返回新插入ID
	 */
	public Integer create(GQueryP query) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(query.getSQLInsert());
			makePSTMT(pstmt, query.getSqlDataCache());
			int num = pstmt.executeUpdate();
			String sqlInsertId = query.getSQLInsertId();
			if (num > 0 && sqlInsertId != null) {
				closePreparedStatement(pstmt);
				pstmt = conn.prepareStatement(sqlInsertId);
				rs = pstmt.executeQuery();
				if (rs.next())
					return rs.getInt(1);
			}
			return num;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeResultSet(rs);
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}
		return 0;
	}

	/**
	 * 删除记录方法
	 * 
	 * @param sql
	 *            sql删除语句
	 * @return 返回执行结果, true/false
	 */
	public boolean delete(String sql) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}
		return false;
	}

	/**
	 * 删除记录方法
	 * 
	 * @param query
	 *            CQuery类
	 * @return 返回执行结果, true/false
	 */
	public boolean delete(GQueryP query) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(query.getSQLDelete());
			makePSTMT(pstmt, query.getSqlDataCache());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}
		return false;
	}

	/**
	 * 更新记录方法
	 * 
	 * @param sql
	 *            sql更新语句
	 * @return 返回执行结果, true/false
	 */
	public boolean update(String sql) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}
		return false;
	}

	/**
	 * 更新记录方法
	 * 
	 * @param query
	 *            CQuery类
	 * @return 返回执行结果, true/false
	 */
	public boolean update(GQueryP query) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(query.getSQLUpdate());
			makePSTMT(pstmt, query.getSqlDataCache());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}
		return false;
	}

	/**
	 * 查询记录数方法
	 * 
	 * @param sql
	 *            sql查询语句
	 * @return 返回记录数
	 */
	public Integer count(String sql) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
				return rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeResultSet(rs);
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}
		return 0;
	}

	/**
	 * 查询记录数方法
	 * 
	 * @param query
	 *            CQuery类
	 * @return 返回记录数
	 */
	public Integer count(GQueryP query) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(query.getSQLCount());
			makePSTMT(pstmt, query.getSqlDataCache());
			rs = pstmt.executeQuery();
			if (rs.next())
				return rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeResultSet(rs);
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}
		return 0;
	}

	/**
	 * 查询单条记录方法
	 * 
	 * @param sql
	 *            sql查询语句
	 * @param index
	 *            标识
	 * @return 返回T
	 */
	public T find(String sql, Integer index) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
				return makeModule(rs, index);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeResultSet(rs);
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}
		return null;
	}

	/**
	 * 查询单条记录方法
	 * 
	 * @param query
	 *            CQuery类
	 * @param index
	 *            标识
	 * @return 返回T
	 */
	public T find(GQueryP query, Integer index) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(query.getSQLSelect(1));
			makePSTMT(pstmt, query.getSqlDataCache());
			rs = pstmt.executeQuery();
			if (rs.next())
				return makeModule(rs, index);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeResultSet(rs);
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}
		return null;
	}

	/**
	 * 查询记录方法
	 * 
	 * @param sql
	 *            sql查询语句
	 * @param index
	 *            标识
	 * @return 返回List<T>
	 */
	public List<T> query(String sql, Integer index) {
		List<T> list = new ArrayList<T>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next())
				list.add(makeModule(rs, index));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeResultSet(rs);
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}
		return list;
	}

	/**
	 * 查询记录方法
	 * 
	 * @param query
	 *            CQuery类
	 * @param index
	 *            标识
	 * @return 返回List<T>
	 */
	public List<T> query(GQueryP query, Integer index) {
		List<T> list = new ArrayList<T>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(query.getSQLSelect());
			makePSTMT(pstmt, query.getSqlDataCache());
			rs = pstmt.executeQuery();
			while (rs.next())
				list.add(makeModule(rs, index));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeResultSet(rs);
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}
		return list;
	}

	/**
	 * 查询记录方法
	 * 
	 * @param query
	 *            CQuery类
	 * @param num
	 *            查询数量
	 * @param index
	 *            标识
	 * @return 返回List<T>
	 */
	public List<T> query(GQueryP query, int num, Integer index) {
		List<T> list = new ArrayList<T>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(query.getSQLSelect(num));
			makePSTMT(pstmt, query.getSqlDataCache());
			rs = pstmt.executeQuery();
			while (rs.next())
				list.add(makeModule(rs, index));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeResultSet(rs);
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}
		return list;
	}

	/**
	 * 翻页查询方法
	 * 
	 * @param query
	 *            CQuery类
	 * @param pageNo
	 *            页面
	 * @param pageSize
	 *            每页数量
	 * @param index
	 *            标识
	 * @return 返回List<T>
	 */
	public List<T> query(GQueryP query, int pageNo, int pageSize, Integer index) {
		List<T> list = new ArrayList<T>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(query.getSQLPage(pageNo, pageSize));
			makePSTMT(pstmt, query.getSqlDataCache());
			rs = pstmt.executeQuery();
			while (rs.next())
				list.add(makeModule(rs, index));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeResultSet(rs);
			closePreparedStatement(pstmt);
			closeConnection(conn);
		}
		return list;
	}

	/**
	 * 批量执行方法
	 * 
	 * @param sqls
	 *            sql语句数组
	 * @return 返回结果数组
	 */
	public int[] executeBatch(String... sqls) {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			for (String sql : sqls)
				stmt.addBatch(sql);
			return stmt.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeStatement(stmt);
			closeConnection(conn);
		}
		return null;
	}

	/**
	 * 批量执行方法
	 * 
	 * @param sqls
	 *            sql语句数组
	 * @return 返回结果数组
	 */
	public int[] executeBatch(List<String> sqls) {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			for (String sql : sqls)
				stmt.addBatch(sql);
			return stmt.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeStatement(stmt);
			closeConnection(conn);
		}
		return null;
	}

	/**
	 * 批量执行暂存数组
	 */
	private List<String> executeBatchList = new ArrayList<String>();

	/**
	 * 添加sql语句到暂存数组
	 * 
	 * @param sql
	 */
	public void addExecuteBatch(String sql) {
		this.executeBatchList.add(sql);
	}

	/**
	 * 批量执行已添加sql语句
	 * 
	 * @return 返回执行结果数组
	 */
	public int[] executeBatch() {
		int[] result = executeBatch(this.executeBatchList);
		this.executeBatchList.clear();
		return result;
	}

	/**
	 * 组建pstmt方法
	 * 
	 * @param pstmt
	 *            PreparedStatement
	 * @param list
	 *            List<Object>
	 * @throws SQLException
	 */
	private void makePSTMT(PreparedStatement pstmt, List<Object> list) throws SQLException {
		int index = 1;
		for (Object value : list) {
			if (value instanceof Integer)
				pstmt.setInt(index, (Integer) value);
			else if (value instanceof String)
				pstmt.setString(index, value.toString());
			else if (value instanceof Long)
				pstmt.setLong(index, (Long) value);
			else if (value instanceof Float)
				pstmt.setFloat(index, (Float) value);
			else if (value instanceof Boolean)
				pstmt.setBoolean(index, (Boolean) value);
			else
				pstmt.setString(index, null);
			index++;
		}
	}

	/**
	 * 组建Module方法
	 * 
	 * @param rs
	 *            ResultSet
	 * @param index
	 *            标识
	 * @return 返回T
	 * @throws SQLException
	 */
	protected abstract T makeModule(ResultSet rs, Integer index) throws SQLException;

	/**
	 * 默认组建Module方法
	 * 
	 * @param rs
	 *            ResultSet
	 * @param fields
	 *            组建module字段, 如果为null表示所有
	 * @return 返回T
	 */
	@SuppressWarnings( { "unchecked" })
	protected T makeModuleAuto(ResultSet rs, String fields) {
		Class<T> entityClass = (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass())
				.getActualTypeArguments()[0];
		Object invokeTester = null;
		try {
			invokeTester = entityClass.newInstance();
			Method methodGetMdbMapping = null;
			TreeMap<String, Method> setMethods = new TreeMap<String, Method>();
			for (Method method : entityClass.getDeclaredMethods()) {
				if (method.getName().startsWith("set"))
					setMethods.put(method.getName(), method);
				else if ("getMdbMapping".equals(method.getName()))
					methodGetMdbMapping = method;
			}
			for (Field f : entityClass.getDeclaredFields()) {
				if (fields == null || fields.toLowerCase().indexOf("[" + f.getName().toLowerCase() + "]") > -1) {
					Method method = setMethods.get("set" + f.getName().substring(0, 1).toUpperCase()
							+ f.getName().substring(1));
					if (method != null) {
						String columnName = f.getName();
						if (methodGetMdbMapping != null) {
							try {
								columnName = (String) methodGetMdbMapping.invoke(invokeTester, columnName);
							} catch (IllegalArgumentException e) {
								e.printStackTrace();
							} catch (InvocationTargetException e) {
								e.printStackTrace();
							}
							if (columnName == null)
								columnName = f.getName();
						}
						try {
							String type = f.getType().toString().toLowerCase();
							if (type.indexOf("string") > -1)
								method.invoke(invokeTester, rs.getString(columnName));
							else if (type.indexOf("int") > -1)
								method.invoke(invokeTester, rs.getInt(columnName));
							else if (type.indexOf("boolean") > -1)
								method.invoke(invokeTester, rs.getBoolean(columnName));
							else if (type.indexOf("long") > -1)
								method.invoke(invokeTester, rs.getLong(columnName));
							else if (type.indexOf("float") > -1)
								method.invoke(invokeTester, rs.getFloat(columnName));
							else if (type.indexOf("double") > -1)
								method.invoke(invokeTester, rs.getDouble(columnName));
						} catch (IllegalArgumentException e) {
							e.printStackTrace();
						} catch (InvocationTargetException e) {
							e.printStackTrace();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
			}
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return (T) invokeTester;
	}

	/**
	 * 关闭conn
	 * 
	 * @param conn
	 *            Connection
	 */
	public void closeConnection(final Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 关闭pstmt
	 * 
	 * @param pstmt
	 *            PreparedStatement
	 */
	public void closePreparedStatement(final PreparedStatement pstmt) {
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 关闭stmt
	 * 
	 * @param stmt
	 *            Statement
	 */
	public void closeStatement(final Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 关闭rs
	 * 
	 * @param rs
	 *            ResultSet
	 */
	public void closeResultSet(final ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 获取暂存批量执行语句
	 * 
	 * @return 返回暂存语句
	 */
	public List<String> getExecuteBatchList() {
		return executeBatchList;
	}

	/**
	 * 设置暂存批量执行语句
	 * 
	 * @param executeBatchList
	 *            设置暂存语句
	 */
	public void setExecuteBatchList(List<String> executeBatchList) {
		this.executeBatchList = executeBatchList;
	}
}
